package com.jsean;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DuckDBUtil {
    private static final String DEFAULT_DB_PATH = "./store/duckdb";
    private static Connection connection;

    // 获取数据库连接
    public static Connection getConnection() throws SQLException {
        if (connection == null || connection.isClosed()) {
            connection = DriverManager.getConnection("jdbc:duckdb:" + DEFAULT_DB_PATH);
        }
        return connection;
    }

    // 执行SQL查询
    public static List<Map<String, Object>> executeQuery(String sql, Object... params) throws SQLException {
        List<Map<String, Object>> result = new ArrayList<>();
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            for (int i = 0; i < params.length; i++) {
                stmt.setObject(i + 1, params[i]);
            }

            ResultSet rs = stmt.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();

            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    row.put(metaData.getColumnName(i), rs.getObject(i));
                }
                result.add(row);
            }
        }
        return result;
    }

    // 执行更新操作
    public static int executeUpdate(String sql, Object... params) throws SQLException {
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            for (int i = 0; i < params.length; i++) {
                stmt.setObject(i + 1, params[i]);
            }
            return stmt.executeUpdate();
        }
    }

    // 创建表
    public static void createTable(String tableName, String schema) throws SQLException {
        String sql = String.format("CREATE TABLE IF NOT EXISTS %s (%s)", tableName, schema);
        executeUpdate(sql);
    }

    // 批量插入数据
    public static void batchInsert(String tableName, List<Map<String, Object>> data) throws SQLException {
        if (data.isEmpty()) return;

        String columns = String.join(",", data.get(0).keySet());
        String placeholders = data.get(0).keySet().stream()
                .map(k -> "?")
                .reduce((a, b) -> a + "," + b)
                .get();

        String sql = String.format("INSERT INTO %s (%s) VALUES (%s)", tableName, columns, placeholders);

        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
            for (Map<String, Object> row : data) {
                int i = 1;
                for (Object value : row.values()) {
                    stmt.setObject(i++, value);
                }
                stmt.addBatch();
            }
            stmt.executeBatch();
        }
    }
}